using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;

namespace TIS.DAO
{
	public class News : DBInteractionBase
	{
		#region Class Member Declarations
			private SqlDateTime		_newsDate, _dateInsert, _dateUpdate;
			private SqlInt32		_languageId, _newsId, _usertUpdate, _userInsert, _newsStatus, _categoryId;
			private SqlString		_newsContent, _newsTitle, _newsMetaDescription, _newsImages, _newsMetaKeyword, _newsMetaTitle, _newsDescription, _newsSEOUrl;
		#endregion


		public News()
		{
			// Nothing for now.
		}


		public override bool Insert()
		{
			SqlCommand	cmdToExecute = new SqlCommand();
			cmdToExecute.CommandText = "dbo.[sp_News_Insert]";
			cmdToExecute.CommandType = CommandType.StoredProcedure;

			// Use base class' connection object
			cmdToExecute.Connection = _mainConnection;

			try
			{
				cmdToExecute.Parameters.Add(new SqlParameter("@iCategoryId", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _categoryId));
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsTitle", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsTitle));
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsContent", SqlDbType.NVarChar, 2000, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsContent));
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsImages", SqlDbType.VarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsImages));
				int	length = 0;
				if(!_newsDescription.IsNull)
				{
					length = _newsDescription.Value.Length;
				}
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsDescription", SqlDbType.NText, length, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsDescription));
				cmdToExecute.Parameters.Add(new SqlParameter("@daNewsDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsDate));
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsMetaTitle", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsMetaTitle));
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsMetaDescription", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsMetaDescription));
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsMetaKeyword", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsMetaKeyword));
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsSEOUrl", SqlDbType.VarChar, 250, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsSEOUrl));
				cmdToExecute.Parameters.Add(new SqlParameter("@iLanguageId", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _languageId));
				cmdToExecute.Parameters.Add(new SqlParameter("@iNewsStatus", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _newsStatus));
				cmdToExecute.Parameters.Add(new SqlParameter("@iUserInsert", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _userInsert));
				cmdToExecute.Parameters.Add(new SqlParameter("@iUsertUpdate", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _usertUpdate));
				cmdToExecute.Parameters.Add(new SqlParameter("@daDateInsert", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _dateInsert));
				cmdToExecute.Parameters.Add(new SqlParameter("@daDateUpdate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _dateUpdate));
				cmdToExecute.Parameters.Add(new SqlParameter("@iNewsId", SqlDbType.Int, 4, ParameterDirection.Output, true, 10, 0, "", DataRowVersion.Proposed, _newsId));

				// Open connection.
				_mainConnection.Open();

				// Execute query.
				cmdToExecute.ExecuteNonQuery();
				_newsId = (Int32)cmdToExecute.Parameters["@iNewsId"].Value;
				return true;
			}
			catch(Exception ex)
			{
				// some error occured. Bubble it to caller and encapsulate Exception object
				throw new Exception("News::Insert::Error occured.", ex);
			}
			finally
			{
				// Close connection.
				_mainConnection.Close();
				cmdToExecute.Dispose();
			}
		}


		public override bool Update()
		{
			SqlCommand	cmdToExecute = new SqlCommand();
			cmdToExecute.CommandText = "dbo.[sp_News_Update]";
			cmdToExecute.CommandType = CommandType.StoredProcedure;

			// Use base class' connection object
			cmdToExecute.Connection = _mainConnection;

			try
			{
				cmdToExecute.Parameters.Add(new SqlParameter("@iNewsId", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Proposed, _newsId));
				cmdToExecute.Parameters.Add(new SqlParameter("@iCategoryId", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _categoryId));
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsTitle", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsTitle));
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsContent", SqlDbType.NVarChar, 2000, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsContent));
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsImages", SqlDbType.VarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsImages));
				int	length = 0;
				if(!_newsDescription.IsNull)
				{
					length = _newsDescription.Value.Length;
				}
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsDescription", SqlDbType.NText, length, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsDescription));
				cmdToExecute.Parameters.Add(new SqlParameter("@daNewsDate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsDate));
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsMetaTitle", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsMetaTitle));
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsMetaDescription", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsMetaDescription));
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsMetaKeyword", SqlDbType.NVarChar, 550, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsMetaKeyword));
				cmdToExecute.Parameters.Add(new SqlParameter("@sNewsSEOUrl", SqlDbType.VarChar, 250, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _newsSEOUrl));
				cmdToExecute.Parameters.Add(new SqlParameter("@iLanguageId", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _languageId));
				cmdToExecute.Parameters.Add(new SqlParameter("@iNewsStatus", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _newsStatus));
				cmdToExecute.Parameters.Add(new SqlParameter("@iUserInsert", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _userInsert));
				cmdToExecute.Parameters.Add(new SqlParameter("@iUsertUpdate", SqlDbType.Int, 4, ParameterDirection.Input, true, 10, 0, "", DataRowVersion.Proposed, _usertUpdate));
				cmdToExecute.Parameters.Add(new SqlParameter("@daDateInsert", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _dateInsert));
				cmdToExecute.Parameters.Add(new SqlParameter("@daDateUpdate", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, _dateUpdate));

				// Open connection.
				_mainConnection.Open();

				// Execute query.
				cmdToExecute.ExecuteNonQuery();
				return true;
			}
			catch(Exception ex)
			{
				// some error occured. Bubble it to caller and encapsulate Exception object
				throw new Exception("News::Update::Error occured.", ex);
			}
			finally
			{
				// Close connection.
				_mainConnection.Close();
				cmdToExecute.Dispose();
			}
		}


		public override bool Delete()
		{
			SqlCommand	cmdToExecute = new SqlCommand();
			cmdToExecute.CommandText = "dbo.[sp_News_Delete]";
			cmdToExecute.CommandType = CommandType.StoredProcedure;

			// Use base class' connection object
			cmdToExecute.Connection = _mainConnection;

			try
			{
				cmdToExecute.Parameters.Add(new SqlParameter("@iNewsId", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Proposed, _newsId));

				// Open connection.
				_mainConnection.Open();

				// Execute query.
				cmdToExecute.ExecuteNonQuery();
				return true;
			}
			catch(Exception ex)
			{
				// some error occured. Bubble it to caller and encapsulate Exception object
				throw new Exception("News::Delete::Error occured.", ex);
			}
			finally
			{
				// Close connection.
				_mainConnection.Close();
				cmdToExecute.Dispose();
			}
		}


		public override DataTable SelectOne()
		{
			SqlCommand	cmdToExecute = new SqlCommand();
			cmdToExecute.CommandText = "dbo.[sp_News_SelectOne]";
			cmdToExecute.CommandType = CommandType.StoredProcedure;
			DataTable toReturn = new DataTable("News");
			SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);

			// Use base class' connection object
			cmdToExecute.Connection = _mainConnection;

			try
			{
				cmdToExecute.Parameters.Add(new SqlParameter("@iNewsId", SqlDbType.Int, 4, ParameterDirection.Input, false, 10, 0, "", DataRowVersion.Proposed, _newsId));

				// Open connection.
				_mainConnection.Open();

				// Execute query.
				adapter.Fill(toReturn);
				if(toReturn.Rows.Count > 0)
				{
					_newsId = (Int32)toReturn.Rows[0]["NewsId"];
					_categoryId = toReturn.Rows[0]["CategoryId"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["CategoryId"];
					_newsTitle = toReturn.Rows[0]["NewsTitle"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["NewsTitle"];
					_newsContent = toReturn.Rows[0]["NewsContent"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["NewsContent"];
					_newsImages = toReturn.Rows[0]["NewsImages"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["NewsImages"];
					_newsDescription = toReturn.Rows[0]["NewsDescription"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["NewsDescription"];
					_newsDate = toReturn.Rows[0]["NewsDate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["NewsDate"];
					_newsMetaTitle = toReturn.Rows[0]["NewsMetaTitle"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["NewsMetaTitle"];
					_newsMetaDescription = toReturn.Rows[0]["NewsMetaDescription"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["NewsMetaDescription"];
					_newsMetaKeyword = toReturn.Rows[0]["NewsMetaKeyword"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["NewsMetaKeyword"];
					_newsSEOUrl = toReturn.Rows[0]["NewsSEOUrl"] == System.DBNull.Value ? SqlString.Null : (string)toReturn.Rows[0]["NewsSEOUrl"];
					_languageId = toReturn.Rows[0]["LanguageId"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["LanguageId"];
					_newsStatus = toReturn.Rows[0]["NewsStatus"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["NewsStatus"];
					_userInsert = toReturn.Rows[0]["UserInsert"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["UserInsert"];
					_usertUpdate = toReturn.Rows[0]["UsertUpdate"] == System.DBNull.Value ? SqlInt32.Null : (Int32)toReturn.Rows[0]["UsertUpdate"];
					_dateInsert = toReturn.Rows[0]["DateInsert"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["DateInsert"];
					_dateUpdate = toReturn.Rows[0]["DateUpdate"] == System.DBNull.Value ? SqlDateTime.Null : (DateTime)toReturn.Rows[0]["DateUpdate"];
				}
				return toReturn;
			}
			catch(Exception ex)
			{
				// some error occured. Bubble it to caller and encapsulate Exception object
				throw new Exception("News::SelectOne::Error occured.", ex);
			}
			finally
			{
				// Close connection.
				_mainConnection.Close();
				cmdToExecute.Dispose();
				adapter.Dispose();
			}
		}


		public override DataTable SelectAll()
		{
			SqlCommand	cmdToExecute = new SqlCommand();
			cmdToExecute.CommandText = "dbo.[sp_News_SelectAll]";
			cmdToExecute.CommandType = CommandType.StoredProcedure;
			DataTable toReturn = new DataTable("News");
			SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);

			// Use base class' connection object
			cmdToExecute.Connection = _mainConnection;

			try
			{

				// Open connection.
				_mainConnection.Open();

				// Execute query.
				adapter.Fill(toReturn);
				return toReturn;
			}
			catch(Exception ex)
			{
				// some error occured. Bubble it to caller and encapsulate Exception object
				throw new Exception("News::SelectAll::Error occured.", ex);
			}
			finally
			{
				// Close connection.
				_mainConnection.Close();
				cmdToExecute.Dispose();
				adapter.Dispose();
			}
		}


		#region Class Property Declarations
		public SqlInt32 NewsId
		{
			get
			{
				return _newsId;
			}
			set
			{
				SqlInt32 newsIdTmp = (SqlInt32)value;
				if(newsIdTmp.IsNull)
				{
					throw new ArgumentOutOfRangeException("NewsId", "NewsId can't be NULL");
				}
				_newsId = value;
			}
		}


		public SqlInt32 CategoryId
		{
			get
			{
				return _categoryId;
			}
			set
			{
				_categoryId = value;
			}
		}


		public SqlString NewsTitle
		{
			get
			{
				return _newsTitle;
			}
			set
			{
				_newsTitle = value;
			}
		}


		public SqlString NewsContent
		{
			get
			{
				return _newsContent;
			}
			set
			{
				_newsContent = value;
			}
		}


		public SqlString NewsImages
		{
			get
			{
				return _newsImages;
			}
			set
			{
				_newsImages = value;
			}
		}


		public SqlString NewsDescription
		{
			get
			{
				return _newsDescription;
			}
			set
			{
				_newsDescription = value;
			}
		}


		public SqlDateTime NewsDate
		{
			get
			{
				return _newsDate;
			}
			set
			{
				_newsDate = value;
			}
		}


		public SqlString NewsMetaTitle
		{
			get
			{
				return _newsMetaTitle;
			}
			set
			{
				_newsMetaTitle = value;
			}
		}


		public SqlString NewsMetaDescription
		{
			get
			{
				return _newsMetaDescription;
			}
			set
			{
				_newsMetaDescription = value;
			}
		}


		public SqlString NewsMetaKeyword
		{
			get
			{
				return _newsMetaKeyword;
			}
			set
			{
				_newsMetaKeyword = value;
			}
		}


		public SqlString NewsSEOUrl
		{
			get
			{
				return _newsSEOUrl;
			}
			set
			{
				_newsSEOUrl = value;
			}
		}


		public SqlInt32 LanguageId
		{
			get
			{
				return _languageId;
			}
			set
			{
				_languageId = value;
			}
		}


		public SqlInt32 NewsStatus
		{
			get
			{
				return _newsStatus;
			}
			set
			{
				_newsStatus = value;
			}
		}


		public SqlInt32 UserInsert
		{
			get
			{
				return _userInsert;
			}
			set
			{
				_userInsert = value;
			}
		}


		public SqlInt32 UsertUpdate
		{
			get
			{
				return _usertUpdate;
			}
			set
			{
				_usertUpdate = value;
			}
		}


		public SqlDateTime DateInsert
		{
			get
			{
				return _dateInsert;
			}
			set
			{
				_dateInsert = value;
			}
		}


		public SqlDateTime DateUpdate
		{
			get
			{
				return _dateUpdate;
			}
			set
			{
				_dateUpdate = value;
			}
		}
		#endregion
	}
}
